Outbound_Process

 
Documentation generated by Matillion ETL

Job: MDM 21 Organization Orchestration

Variables:

Name
Visibility
Behaviour
Type
Default Value
Description
currentDT
Public
Shared
Text
 
 

get current date time

Python Script
Parameter
Value
Script
from datetime import datetime

currentDT = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print (currentDT)
context.updateVariable("currentDT",currentDT)
Interpreter
Python 3
Timeout
360

Process: (A) MDM21 Organization (Get Hierarchy Info)

Run Transformation
Parameter
Value
Transformation Job
(A) MDM21 Organization (Get Hierarchy Info)
Set Scalar Variables
Set Grid Variables

Process: (B) MDM21 Organization

Run Transformation
Parameter
Value
Transformation Job
(B) MDM21 Organization
Set Scalar Variables
Set Grid Variables

S3 Unload: MDM21 Organization

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
eth mdm21 organization
S3 URL Location
${MDM21_S3_Target_Folder}
S3 Object Prefix
ETH MDM21 Organization.txt
IAM Role Arn
arn:aws:iam::<aws-account-id>:role/<role-name>
Generate Manifest
No
Data File Type
Delimited
Delimiter
\t
Compress Data
No
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

File Transfer SFTP: MDM21 Organization

Data Transfer Object
Parameter
Value
Source Type
S3
Source URL
s3://ethicon/outbound/Outbound_Process/MDM21/ETH MDM21 Organization.txt000
Unpack ZIP file
No
Target Type
SFTP
Gzip data
No
Target Object Name
ETH MDM21 Organization.txt
Set Home Directory as Root
No
Target URL
https://smft.axtria.com/MDM21
Target Username
JnJ_Ethicon
Target Password
********
Target SFTP Key

Set Current Table File For Archive

Python Script
Parameter
Value
Script
context.updateVariable('Archive_temp_current_table', 'eth mdm21 organization')
context.updateVariable('Archive_temp_current_file', 'ETH MDM21 Organization.txt')
context.updateVariable('Archive_type','MDM21')
Interpreter
Python 3
Timeout
360

Archive Outbound Global Function 0

Run Orchestration
Parameter
Value
Orchestration Job
Archive Outbound Global Function
Set Scalar Variables
Set Grid Variables

Job: (A) MDM21 Organization (Get Hierarchy Info)

CG Geography: Get Fiscal Calendar Information
Get Geography Upper and Lower Geo Information
Geography Hierarchy : Get Fiscal Calendar Information
Parameter: Geography Hierarchy
Process Lower and Upper Hierarchy Level Information
stg_MDM21_Organization_Geo_Terr_n_Mgr - Staging Table

11/2/2019 - 8,793
stg_MDM21_Geo_Hier_Terr_n_Upper - Staging Table

11/2/2019 - 11,535

outbound_dest_fiscal_calendar

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_fiscal_calendar
Column Names
efftv_start_dt, efftv_end_dt, datamonth, fiscal_year_month, fiscal_year_wk_start_dt, fiscal_year_wk_end_dt
Trim Columns
No

Geo Hier Start Date

Join
Parameter
Value
Main Table
Geo Hier: Rename
Main Table Alias
filter
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
"filter"."efftv_start_dt"="fiscal"."efftv_start_dt", filter_Left_fiscal
Output Columns
filter.lower_algn_struc_cd, lower_algn_struc_cd, filter.lower_geo_id, lower_geo_id, filter.upper_algn_struc_cd, upper_algn_struc_cd, filter.upper_geo_id, upper_geo_id, filter.efftv_start_dt, filter_efftv_start_dt, filter.efftv_end_dt, filter_efftv_end_dt, filter.last_updated, last_updated, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Geo Hier End Date

Join
Parameter
Value
Main Table
Geo Hier Start Date
Main Table Alias
start
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
"start"."filter_efftv_end_dt"="fiscal"."efftv_end_dt", start_Left_fiscal
Output Columns
start.lower_algn_struc_cd, lower_algn_struc_cd, start.lower_geo_id, lower_geo_id, start.upper_algn_struc_cd, upper_algn_struc_cd, start.upper_geo_id, upper_geo_id, start.filter_efftv_start_dt, filter_efftv_start_dt, start.filter_efftv_end_dt, filter_efftv_end_dt, start.last_updated, last_updated, start.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt

Geo Hier Convert to Fiscal

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "fiscal_year_wk_start_dt" is NULL THEN
'1900-01-01'
ELSE
"fiscal_year_wk_start_dt"
END, EFFTV_START_DT_new, CASE WHEN "fiscal_year_wk_end_dt" is NULL THEN
'9999-12-31'
ELSE
"fiscal_year_wk_end_dt"
END, EFFTV_END_DT_new, DATE_PART_YEAR(to_date("last_updated",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("last_updated",'YYYY-mm-dd'))),2,'00'), LAST_UPDATED_DT_new, len("lower_geo_id"), len_geo_id, "upper_algn_struc_cd"!="lower_algn_struc_cd", compare struc code

Filter Geo Hier

Filter
Parameter
Value
Filter Conditions
len_geo_id, Not, Equal to, 4
Combine Conditions
AND

Geo Hier: Filter Exclude Upper Struc Not Equal Lower

SQL
Parameter
Value
SQL Query
SELECT
*
FROM ($T{Filter Geo Hier})
WHERE (NOT(("upper_geo_id" = '80')
AND UPPER_ALGN_STRUC_CD <> LOWER_ALGN_STRUC_CD))

Geo Hier Expand by Upper Geo

Join
Parameter
Value
Main Table
Geo Hier: Filter Exclude Upper Struc Not Equal Lower
Main Table Alias
c1
Joins
level CD is territory, c2, Left
Join Expressions
"c1"."upper_algn_struc_cd" = "c2"."upper_algn_struc_cd"
and "c1"."efftv_start_dt_new" <= "c2"."efftv_end_dt_new"
and "c1"."efftv_end_dt_new" >= "c2"."efftv_start_dt_new"
, c1_Left_c2
Output Columns
c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Geo Hier Merge Terr and Div

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

Geo Hier Get Dates for Upper

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
case
when "efftv_start_dt">"new_efftv_start_dt"
then "efftv_start_dt"
else "new_efftv_start_dt"
end, EFFTV_START_DT_2, case
when "efftv_end_dt">"new_efftv_end_dt"
then "new_efftv_end_dt"
else "efftv_end_dt"
end, EFFTV_END_DT_2, case
when len("lower_geo_id")=2
then "lower_algn_struc_cd"
else "upper_geo_id"
end, UPPER_GEO_ID_2

Geo Hier Get Dates for Upper(included recs)

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, lower_algn_struc_cd, lower_geo_id, lower_geo_id, lower_algn_struc_cd, upper_algn_struc_cd, upper_geo_id_2, upper_geo_id, efftv_start_dt_2, efftv_start_dt, efftv_end_dt_2, efftv_end_dt

Geo Hier Merge Terr and Upper

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

stg_MDM21_Geo_Hier_Terr_n_Upper

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
stg_mdm21_geo_hier_terr_n_upper
Fix Data Type Mismatches
No
Column Mapping
upper_geo_id, upper_geo_id, lower_geo_id, lower_geo_id, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, lower_algn_struc_cd, lower_algn_struc_cd, upper_algn_struc_cd, upper_algn_struc_cd
Truncate
Truncate
Automatic Compression
No

Geo_Id =3 digit

Filter
Parameter
Value
Filter Conditions
len_geo_id, Is, Equal to, 3
Combine Conditions
AND

Geo Hier Expand by Upper Div

Join
Parameter
Value
Main Table
Geo_Id =3 digit
Main Table Alias
c1
Joins
level CD is division, c2, Left
Join Expressions
"c1"."upper_algn_struc_cd" = "c2"."upper_algn_struc_cd"
and "c1"."lower_algn_struc_cd" = "c2"."lower_algn_struc_cd"
and "c1"."efftv_start_dt_new" <= "c2"."efftv_end_dt_new"
and "c1"."efftv_end_dt_new" >= "c2"."efftv_start_dt_new", c1_Left_c2
Output Columns
c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Geo_Id =2 digit

Filter
Parameter
Value
Filter Conditions
len_geo_id, Is, Equal to, 2
Combine Conditions
AND

Geo Hier Expand by Upper Reg

Join
Parameter
Value
Main Table
Geo_Id =2 digit
Main Table Alias
c1
Joins
level CD is division, c2, Left
Join Expressions
"c1"."upper_algn_struc_cd"="c2"."upper_algn_struc_cd"
and
"c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new"
and
"c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new", c1_Left_c2
Output Columns
c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Geo_Id =4 digit

Filter
Parameter
Value
Filter Conditions
len_geo_id, Is, Equal to, 4
Combine Conditions
AND

Geo_Id =4 digit (included recs)

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, lower_algn_struc_cd, lower_geo_id, lower_geo_id, upper_algn_struc_cd, upper_algn_struc_cd, upper_geo_id, upper_geo_id, efftv_start_dt_new, efftv_start_dt, efftv_end_dt_new, efftv_end_dt

Get Start Date

Join
Parameter
Value
Main Table
Geos: Rename Cols
Main Table Alias
filter
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
cast("filter"."efftv_start_dt" as date)= cast("fiscal"."efftv_start_dt" as date), filter_Left_fiscal
Output Columns
filter.algn_struc_cd, algn_struc_cd, filter.geo_id, geo_id, filter.geo_nm, geo_nm, filter.level_cd, level_cd, filter.efftv_start_dt, filter_efftv_start_dt, filter.efftv_end_dt, filter_efftv_end_dt, filter.last_updated, last_updated, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Get End Date

Join
Parameter
Value
Main Table
Get Start Date
Main Table Alias
start
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
cast("start"."filter_efftv_end_dt" as date) = cast("fiscal"."efftv_end_dt" as date), start_Left_fiscal
Output Columns
start.algn_struc_cd, algn_struc_cd, start.geo_id, geo_id, start.geo_nm, geo_nm, start.level_cd, level_cd, start.filter_efftv_start_dt, filter_efftv_start_dt, start.filter_efftv_end_dt, filter_efftv_end_dt, start.last_updated, last_updated, start.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt

Geo convert to Fiscal

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE
WHEN "fiscal_year_wk_start_dt" is NULL
THEN '1900-01-01'
ELSE "fiscal_year_wk_start_dt"
END, EFFTV_START_DT_new, CASE
WHEN "fiscal_year_wk_end_dt" is NULL
THEN '9999-12-31'
ELSE "fiscal_year_wk_end_dt"
END, EFFTV_END_DT_new, DATE_PART_YEAR(to_date("last_updated",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("last_updated",'YYYY-mm-dd'))),2,'00'), LAST_UPDATED_DT_new, len("geo_id"), geo_id_length

GEO_ID is not like ????

Filter
Parameter
Value
Filter Conditions
geo_id, Not, Like, ____
Combine Conditions
AND

Expand by Upper

Join
Parameter
Value
Main Table
GEO_ID is not like ????
Main Table Alias
c1
Joins
level CD is territory, c2, Left
Join Expressions
"c1"."algn_struc_cd"="c2"."upper_algn_struc_cd"
and "c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new"
and "c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new"
, c1_Left_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.geo_nm, geo_nm, c1.level_cd, level_cd, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c1.last_updated_dt_new, last_updated_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Merge Terr and Div

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
Yes

Get Dates for Upper

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
case when "efftv_start_dt">"new_efftv_start_dt" then
"efftv_start_dt"
else
"new_efftv_start_dt"
end, EFFTV_START_DT_2, case when "efftv_end_dt">"new_efftv_end_dt" then
"new_efftv_end_dt"
else
"efftv_end_dt"
end, EFFTV_END_DT_2, case when "level_cd"='Nation' then
"lower_algn_struc_cd"
else
"geo_id"
end, GEO_ID_2

Refined output 2

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, algn_struc_cd, geo_id_2, geo_id, level_cd, level_cd, geo_nm, geo_nm, efftv_start_dt_2, efftv_start_dt, efftv_end_dt_2, efftv_end_dt

Combine Terr and Mgr

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

stg_MDM21_Organization_Geo_Terr_n_Mgr

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
stg_mdm21_organization_geo_terr_n_mgr
Fix Data Type Mismatches
No
Column Mapping
algn_struc_cd, algn_struc_cd, geo_id, geo_id, level_cd, level_cd, geo_nm, geo_nm, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt
Truncate
Truncate
Automatic Compression
No

Geo Id =4 digit

Filter
Parameter
Value
Filter Conditions
geo_id_length, Is, Equal to, 4
Combine Conditions
AND

Refined output

Rename
Parameter
Value
Column Mapping
algn_struc_cd, algn_struc_cd, geo_id, geo_id, level_cd, level_cd, geo_nm, geo_nm, efftv_start_dt_new, efftv_start_dt, efftv_end_dt_new, efftv_end_dt

Geo Id =2 digit

Filter
Parameter
Value
Filter Conditions
geo_id_length, Is, Equal to, 2
Combine Conditions
AND

Expand by Upper Div

Join
Parameter
Value
Main Table
Geo Id =2 digit
Main Table Alias
c1
Joins
level CD is division, c2, Left
Join Expressions
"c1"."algn_struc_cd" = "c2"."upper_algn_struc_cd"
and "c1"."efftv_start_dt_new" <= "c2"."efftv_end_dt_new"
and "c1"."efftv_end_dt_new" >= "c2"."efftv_start_dt_new"
, c1_Left_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.geo_nm, geo_nm, c1.level_cd, level_cd, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c1.last_updated_dt_new, last_updated_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Expand by Upper Div (included recs)

Filter
Parameter
Value
Filter Conditions
lower_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Param Geo Hier Start

Join
Parameter
Value
Main Table
Parameter: Geo Hierarchy
Main Table Alias
geo_hier
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
cast("geo_hier"."efftv_start_dt" as date)= cast("fiscal"."efftv_start_dt" as date), geo_hier_Left_fiscal
Output Columns
geo_hier.upper_algn_struc_cd, upper_algn_struc_cd, geo_hier.lower_algn_struc_cd, lower_algn_struc_cd, geo_hier.efftv_start_dt, geo_hier_efftv_start_dt, geo_hier.efftv_end_dt, geo_hier_efftv_end_dt, geo_hier.level_cd, level_cd, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Param Geo Hier End

Join
Parameter
Value
Main Table
Param Geo Hier Start
Main Table Alias
geo_hier
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
cast("geo_hier"."geo_hier_efftv_end_dt" as date)= cast("fiscal"."efftv_end_dt" as date), geo_hier_Left_fiscal
Output Columns
geo_hier.upper_algn_struc_cd, upper_algn_struc_cd, geo_hier.lower_algn_struc_cd, lower_algn_struc_cd, geo_hier.geo_hier_efftv_start_dt, efftv_start_dt, geo_hier.geo_hier_efftv_end_dt, efftv_end_dt, geo_hier.level_cd, level_cd, geo_hier.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt

Param Geo Hier Calc Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE
WHEN "fiscal_year_wk_start_dt" is NULL
THEN '1900-01-01'
ELSE "fiscal_year_wk_start_dt"
END, EFFTV_START_DT_new, CASE
WHEN "fiscal_year_wk_end_dt" is NULL
THEN '9999-12-31'
ELSE "fiscal_year_wk_end_dt"
END, EFFTV_END_DT_new

level CD is territory

Filter
Parameter
Value
Filter Conditions
level_cd, Is, Ilike, Territory
Combine Conditions
AND

level CD is division

Filter
Parameter
Value
Filter Conditions
level_cd, Is, Equal to, Division
Combine Conditions
AND

Parameter: Geo Hierarchy

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_geo_hierarchy_structure
Column Names
upper_algn_struc_cd, lower_algn_struc_cd, efftv_start_dt, efftv_end_dt, level_cd
Trim Columns
No

outbound_src_geo_hierarchy

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_param_geo_hierarchy
Column Names
lower_algn_struc_cd, lower_geo_id, upper_algn_struc_cd, upper_geo_id, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Geo Hier: Rename

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, lower_algn_struc_cd, lower_geo_id, lower_geo_id, upper_algn_struc_cd, upper_algn_struc_cd, upper_geo_id, upper_geo_id, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, last_updated_dt, last_updated

CG Geographies Mgmt

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_geo_master_outbound
Column Names
algn_struc_cd, geo_id, geo_nm, level_cd, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Geos: Rename Cols

Rename
Parameter
Value
Column Mapping
algn_struc_cd, algn_struc_cd, geo_id, geo_id, geo_nm, geo_nm, level_cd, level_cd, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, last_updated_dt, last_updated

Job: (B) MDM21 Organization

Variables:

Name
Visibility
Behaviour
Type
Default Value
Description
v_CURRENT_DATE
Public
Copied
DateTime
2019-01-01
The fiscal year
stg_MDM21_Geo_Hier_Terr_n_Upper
stg_MDM21_Organization_Geo_Terr_n_Mgr
Post to MDM21 ETH MDM21 Organization
Post to MDM Prior Org
MDM LOV Parameter

outbound_src_mdm_lov_param

Table Input
Parameter
Value
Schema
${Schema_2}
Table Name
outbound_src_mdm_lov_param
Column Names
alignment structure code, franchise code, org id prefix, sales org division code, sales org name, default bill-to, default pay-from, updated by, updated date
Trim Columns
No

Get Organization ID

Join
Parameter
Value
Main Table
Combine Geos and Teams
Main Table Alias
c1
Joins
outbound_src_mdm_lov_param, c2, Inner
Join Expressions
"c1"."algn_struc_cd"="c2"."alignment structure code", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.team_id, team_id, c2.org id prefix, org id prefix, c2.franchise code, franchise code, c2.sales org division code, sales org division code, c2.sales org name, sales org name

Filter_dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
"efftv_start_dt"<=CURRENT_DATE
and
"efftv_end_dt">=CURRENT_DATE, flag

Get Organization ID (included recs)

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, true
Combine Conditions
AND

Get Upper Terr & Dates

Join
Parameter
Value
Main Table
Get Organization ID (included recs)
Main Table Alias
c1
Joins
included recs, c2, Left
Join Expressions
"c1"."algn_struc_cd"="c2"."lower_algn_struc_cd"
and
"c1"."geo_id"="c2"."lower_geo_id"
and
"c1"."efftv_start_dt"<="c2"."efftv_end_dt"
and
"c1"."efftv_end_dt">="c2"."efftv_start_dt"
, c1_Left_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c2.upper_geo_id, upper_geo_id, c2.efftv_start_dt, new_efftv_start_dt, c2.efftv_end_dt, new_efftv_end_dt

Update Upper Terr & Constants

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "upper_geo_id" = "algn_struc_cd" THEN
"geo_id"
ELSE
"upper_geo_id"
END
, upper_geo_id, 'OPCO_END', Provider ID, CURRENT_DATE, last_updated_dt, GREATEST (cast("efftv_start_dt" as date), cast("new_efftv_start_dt" as date))
, efftv_start_dt, LEAST (cast("efftv_end_dt" as date), cast("new_efftv_end_dt" as date))
, efftv_end_dt

Get Upper Territory Level

Join
Parameter
Value
Main Table
Update Upper Terr & Constants
Main Table Alias
c1
Joins
flag(included recs), c2, Inner
Join Expressions
"c1"."algn_struc_cd"="c2"."algn_struc_cd"
AND "c1"."upper_geo_id"="c2"."geo_id", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c1.upper_geo_id, upper_geo_id, c1.new_efftv_start_dt, new_efftv_start_dt, c1.new_efftv_end_dt, new_efftv_end_dt, c1.provider id, provider id, c1.last_updated_dt, last_updated_dt, c2.level_cd, upper_level_cd

Get Original Align Struct

Join
Parameter
Value
Main Table
Get Upper Territory Level
Main Table Alias
gutl
Joins
Filter Active Geographies, fag, Left
Join Expressions
"gutl"."geo_id" = "fag"."geo_id", gutl_Left_fag
Output Columns
gutl.algn_struc_cd, algn_struc_cd, gutl.geo_id, geo_id, gutl.team_id, team_id, gutl.level_cd, level_cd, gutl.geo_nm, geo_nm, gutl.efftv_start_dt, efftv_start_dt, gutl.efftv_end_dt, efftv_end_dt, gutl.org id prefix, org id prefix, gutl.franchise code, franchise code, gutl.sales org division code, sales org division code, gutl.sales org name, sales org name, gutl.upper_geo_id, upper_geo_id, gutl.new_efftv_start_dt, new_efftv_start_dt, gutl.new_efftv_end_dt, new_efftv_end_dt, gutl.provider id, provider id, gutl.last_updated_dt, last_updated_dt, gutl.upper_level_cd, upper_level_cd, fag.algn_struc_cd, fag_algn_struc_cd

Update Sales Org Name

Join
Parameter
Value
Main Table
Get Original Align Struct
Main Table Alias
goas
Joins
MDM LOV Parameter, lovp, Left
Join Expressions
"goas"."fag_algn_struc_cd" = "lovp"."alignment structure code", goas_Left_lovp
Output Columns
goas.algn_struc_cd, algn_struc_cd, goas.geo_id, geo_id, goas.team_id, team_id, goas.level_cd, level_cd, goas.geo_nm, geo_nm, goas.efftv_start_dt, efftv_start_dt, goas.efftv_end_dt, efftv_end_dt, goas.org id prefix, org id prefix, goas.franchise code, franchise code, goas.sales org division code, sales org division code, goas.sales org name, sales org name, goas.upper_geo_id, upper_geo_id, goas.new_efftv_start_dt, new_efftv_start_dt, goas.new_efftv_end_dt, new_efftv_end_dt, goas.provider id, provider id, goas.last_updated_dt, last_updated_dt, goas.upper_level_cd, upper_level_cd, lovp.sales org name, lovp_sales org name

Update Final Constants

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "level_cd" ilike '%terr%' THEN
"team_id"
ELSE
"org id prefix" || "team_id"
END, geo_id, left("upper_level_cd",1), parent organization id, TRIM("geo_nm")
, geo_nm, CASE WHEN left("level_cd",1) = 'S' THEN
'T'
ELSE
left("level_cd",1)
END
, organization id, CASE WHEN DATE_PART_YEAR(to_date("efftv_end_dt",'yyyy-mm-dd')) ='9999' THEN
NULL
ELSE
"efftv_end_dt"
END, efftv_end_dt, "org id prefix" || "upper_geo_id"

, upper_geo_id

Get Oldest

Join
Parameter
Value
Main Table
Update Final Constants
Main Table Alias
ufc
Joins
Agg for Oldest, afo, Inner
Join Expressions
cast("ufc"."efftv_start_dt" as date) = cast("afo"."min_efftv_start_dt" as date)
AND "ufc"."geo_id" = "afo"."geo_id"
AND "ufc"."organization id" = "afo"."organization id"
AND "ufc"."parent organization id" = "afo"."parent organization id"
AND "ufc"."upper_geo_id" = "afo"."upper_geo_id", ufc_Inner_afo
Output Columns
ufc.algn_struc_cd, algn_struc_cd, ufc.level_cd, level_cd, ufc.team_id, team_id, ufc.org id prefix, org id prefix, ufc.franchise code, franchise code, ufc.sales org division code, sales org division code, ufc.lovp_sales org name, sales org name, ufc.new_efftv_start_dt, new_efftv_start_dt, ufc.new_efftv_end_dt, new_efftv_end_dt, ufc.provider id, provider id, ufc.last_updated_dt, last_updated_dt, ufc.efftv_start_dt, efftv_start_dt, ufc.upper_level_cd, upper_level_cd, ufc.upper_geo_id, upper_geo_id, ufc.geo_id, geo_id, ufc.parent organization id, parent organization id, ufc.geo_nm, geo_nm, ufc.organization id, organization id, ufc.efftv_end_dt, efftv_end_dt

Aggregate Before Export

Aggregate
Parameter
Value
Groupings
provider id, geo_id, organization id, parent organization id, upper_geo_id, efftv_start_dt, efftv_end_dt, last_updated_dt
Aggregations
geo_nm, Min, sales org division code, Min, sales org name, Min, franchise code, Min

MDM21 Org: Format Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
DATE_PART_YEAR(to_date("efftv_start_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("efftv_start_dt",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("efftv_start_dt",'YYYY-mm-dd'))),2,'00')
, efftv_start_dt, DATE_PART_YEAR(to_date("efftv_end_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("efftv_end_dt",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("efftv_end_dt",'YYYY-mm-dd'))),2,'00')
, efftv_end_dt, DATE_PART_YEAR(to_date("last_updated_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated_dt",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("last_updated_dt",'YYYY-mm-dd'))),2,'00')

, last_updated_dt

MDM Org: Rename Columns

Rename
Parameter
Value
Column Mapping
provider id, Provider Id, geo_id, Organization ID, organization id, Organization Type Code, min_geo_nm, Organization Name, min_sales org division code, Sales Org Division Code, min_sales org name, Sales Org Team Code, min_franchise code, Franchise Code, upper_geo_id, Parent Organization ID, parent organization id, Parent Organization Type Code, efftv_start_dt, Organization Effective Date, efftv_end_dt, Organization End Date, last_updated_dt, Last Update Date

Post to MDM21 ETH MDM21 Organization

Temporary Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
ETH MDM21 Organization
Table Sort Key
Table Distribution Style
Even

Agg for Oldest

Aggregate
Parameter
Value
Groupings
geo_id, organization id, parent organization id, upper_geo_id
Aggregations
efftv_start_dt, Min

Aggregate for Prior Org

Aggregate
Parameter
Value
Groupings
algn_struc_cd, team_id
Aggregations
algn_struc_cd, Min, team_id, Min

Post to MDM Prior Org

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
outbound_dest_mdm21_prior_org
Fix Data Type Mismatches
No
Column Mapping
min_team_id, geo_id, min_algn_struc_cd, algn_struc_cd
Truncate
Truncate
Automatic Compression
No

Calculator 0

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
sysdate, last_updated

Post to MDM Prior Org_bkp

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
outbound_dest_mdm21_prior_org_bkp
Fix Data Type Mismatches
No
Column Mapping
min_team_id, geo_id, min_algn_struc_cd, algn_struc_cd, last_updated, last_updated
Truncate
Append

outbound_src_cust_team_geo_association

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_customerteam_geo_assoc
Column Names
algn_struc_cd, team_id, geo_id, split_pct, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

calculate dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
"efftv_start_dt"<=CURRENT_DATE
and
"efftv_end_dt">=CURRENT_DATE, flag, "team_id" like 'T%', team_id-refined

Cust Team Geo (Included recs)

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, true, team_id-refined, Is, Equal to, true
Combine Conditions
AND

Get Valid Teams

Join
Parameter
Value
Main Table
Cust Team Geo (Included recs)
Main Table Alias
c1
Joins
Exclude rolliing to 80, c2, Inner
Join Expressions
"c1"."algn_struc_cd"="c2"."algn_struc_cd"
and
"c1"."geo_id"="c2"."geo_id", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.split_pct, split_pct, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt

Agg for Largest Team Record

Aggregate
Parameter
Value
Groupings
algn_struc_cd, team_id
Aggregations
split_pct, Max

Get Max Team ID

Join
Parameter
Value
Main Table
Get Valid Teams
Main Table Alias
c1
Joins
Agg for Largest Team Record, c2, Inner
Join Expressions
"c1"."algn_struc_cd"="c2"."algn_struc_cd"
and
"c1"."split_pct"="c2"."max_split_pct"
and
"c1"."team_id"="c2"."team_id", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.split_pct, split_pct, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt

One Record Per Team

Aggregate
Parameter
Value
Groupings
algn_struc_cd, team_id
Aggregations
geo_id, Max

Get Team Info

Join
Parameter
Value
Main Table
One Record Per Team
Main Table Alias
c1
Joins
Exclude rolliing to 80, c2, Inner
Join Expressions
"c1"."algn_struc_cd"="c2"."algn_struc_cd"
and
"c1"."max_geo_id"="c2"."geo_id", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.max_geo_id, geo_id, c2.level_cd, level_cd, c2.geo_nm, geo_nm, c2.efftv_start_dt, efftv_start_dt, c2.efftv_end_dt, efftv_end_dt, c1.team_id, team_id

Combine Geos and Teams

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

stg_MDM21_Organization_Geo_Terr_n_Mgr

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
stg_mdm21_organization_geo_terr_n_mgr
Column Names
algn_struc_cd, geo_id, level_cd, geo_nm, efftv_start_dt, efftv_end_dt
Trim Columns
No

Filter on dates

SQL
Parameter
Value
SQL Query
SELECT
"algn_struc_cd",
"geo_id",
"level_cd",
"geo_nm",
"efftv_start_dt",
"efftv_end_dt",
("efftv_start_dt" <= CAST(getdate() as date)
AND "efftv_end_dt" >= CAST(getdate() as date))
AS "flag"
FROM ($T{stg_MDM21_Organization_Geo_Terr_n_Mgr})



flag(included recs)

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, true
Combine Conditions
AND

Exclude Rolling to 80 (included recs)

Join
Parameter
Value
Main Table
flag(included recs)
Main Table Alias
c1
Joins
Combine Rolling to 80, c2, Left
Join Expressions
"c1"."algn_struc_cd" = "c2"."lower_algn_struc_cd"
and "c1"."geo_id" = "c2"."lower_geo_id", c1_Left_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.geo_id, team_id, c2.lower_algn_struc_cd, lower_right

Exclude rolliing to 80

Filter
Parameter
Value
Filter Conditions
lower_right, Is, Null or blank
Combine Conditions
AND

Filter on dates 1

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
"efftv_start_dt"<=CURRENT_DATE
and
"efftv_end_dt">=CURRENT_DATE, flag

stg_MDM21_Geo_Hier_Terr_n_Upper

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
stg_mdm21_geo_hier_terr_n_upper
Column Names
upper_algn_struc_cd, upper_geo_id, lower_algn_struc_cd, lower_geo_id, efftv_start_dt, efftv_end_dt
Trim Columns
No

Get Territories Rolling to 80: Filter dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "efftv_start_dt" <= CURRENT_DATE and "efftv_end_dt" >= CURRENT_DATE THEN
1
ELSE
0
END, flag

Get Territories Rolling to 80: Filter Yes

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, 1
Combine Conditions
AND

Get Territories Rolling to 80

Join
Parameter
Value
Main Table
Get Territories Rolling to 80: Filter Yes
Main Table Alias
c1
Joins
Geo Hier Merge Terr and Upper (included recs), c2, Inner
Join Expressions
"c1"."upper_algn_struc_cd"= "c2"."lower_algn_struc_cd" and
"c1"."upper_geo_id"="c2"."lower_geo_id"
, c1_Inner_c2
Output Columns
c1.lower_algn_struc_cd, c1_lower_algn_struc_cd, c1.lower_geo_id, c1_lower_geo_id, c1.upper_algn_struc_cd, c1_upper_algn_struc_cd, c1.upper_geo_id, c1_upper_geo_id, c1.efftv_start_dt, c1_efftv_start_dt, c1.efftv_end_dt, c1_efftv_end_dt, c1.flag, c1_flag, c2.lower_algn_struc_cd, c2_lower_algn_struc_cd, c2.lower_geo_id, c2_lower_geo_id, c2.upper_algn_struc_cd, c2_upper_algn_struc_cd, c2.upper_geo_id, c2_upper_geo_id, c2.efftv_start_dt, c2_efftv_start_dt, c2.efftv_end_dt, c2_efftv_end_dt, c2.flag, c2_flag

Copy of Rename output

Rename
Parameter
Value
Column Mapping
c1_lower_algn_struc_cd, lower_algn_struc_cd, c1_lower_geo_id, lower_geo_id, c1_upper_algn_struc_cd, upper_algn_struc_cd, c1_upper_geo_id, upper_geo_id, c1_efftv_start_dt, efftv_start_dt, c1_efftv_end_dt, efftv_end_dt

Combine Rolling to 80

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

Geo Hier Merge Terr and Upper (geo id =80)

Filter
Parameter
Value
Filter Conditions
lower_geo_id, Is, Equal to, 80
Combine Conditions
AND

Filter dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "efftv_start_dt" <= CURRENT_DATE and "efftv_end_dt" >= CURRENT_DATE THEN
1
ELSE
0
END, flag

included recs

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, true
Combine Conditions
AND

Geo Hier Merge Terr and Upper (included recs)

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, 1, upper_geo_id, Is, Equal to, 80
Combine Conditions
AND

Rename output

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, lower_algn_struc_cd, lower_geo_id, lower_geo_id, upper_algn_struc_cd, upper_algn_struc_cd, upper_geo_id, upper_geo_id, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt

MDM LOV Parameter

Table Input
Parameter
Value
Schema
${Schema_2}
Table Name
outbound_src_mdm_lov_param
Column Names
alignment structure code, franchise code, org id prefix, sales org division code, sales org name, default bill-to, default pay-from, updated by, updated date
Trim Columns
No

CG Geographies Mgmt

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_geo_master_outbound
Column Names
algn_struc_cd, geo_id, geo_nm, level_cd, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Filter Active Geographies

SQL
Parameter
Value
SQL Query
SELECT
*
FROM ($T{CG Geographies Mgmt})
WHERE ("efftv_start_dt" <= CAST(getdate() as date)
AND "efftv_end_dt" >= CAST(getdate() as date))

Job: Archive Outbound Global Function

Add TimeStamp & Get File,Table Information from context of the job run

Python Script
Parameter
Value
Script
import datetime

x = datetime.datetime.now()
x=str(x).split('.')

x=x[0]
print('time_stamp :'+str(x))
print('Archive_temp_current_table :'+str(Archive_temp_current_table))
print('Archive_temp_current_file :'+str(Archive_temp_current_file))

Archive_temp_current_file=Archive_temp_current_file+'_'+x+'__'
print('Archive_temp_current_file upt :'+str(Archive_temp_current_file))
context.updateVariable('Archive_temp_current_file', str(Archive_temp_current_file))
print('Archive_type :'+Archive_type)


#dynamically change path for Archive
if Archive_type=='ONEMD':
S3_temp_Archive_Location=S3_ONEMD_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))
elif Archive_type=='MDM21':
S3_temp_Archive_Location=S3_MDM21_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))
else:
S3_temp_Archive_Location=S3_EUSS_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))




Interpreter
Jython

If ONEMD

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, ONEMD
Combine Conditions
And

S3 Archive(ONEMD)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_ONEMD_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

If MDM21

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, MDM21
Combine Conditions
And

S3 Archive(MDM21)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_MDM21_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

If EUSS

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, EUSS
Combine Conditions
And

S3 Archive(EUSS)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_EUSS_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None